package com.red.wood.module.eval.mybatis.service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.red.wood.common.springjdbc.CommonDao;
import com.red.wood.common.util.CommonUtils;

@Service
public class EvalExcelIncImport {

	private static final Log log = LogFactory.getLog(EvalExcelIncImport.class);
	
	@Resource(name="jdbcTemplate")
	private JdbcTemplate jdbcTemplate;
	
	@Autowired
	private CommonDao commonDao;
	
	public Map<String,List<String>> getQueryMaster(){
		String sql = "select evalNum,enterprise_name from t_eval_result_query_master";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
		
		List<String> evalNumList = new ArrayList<String>();
		List<String> evalEnterNameList = new ArrayList<String>();
		
		if(list != null && list.size() > 0) {
			for(Map<String,Object> map : list) {
				String evalNum = CommonUtils.valueOf(map.get("evalNum"));
				String enterprise_name = CommonUtils.valueOf(map.get("enterprise_name"));
				evalNumList.add(evalNum);
				evalEnterNameList.add(enterprise_name);
			}
		}
		Map<String,List<String>> map = new HashMap<String,List<String>>();
		map.put("evalNum", evalNumList);
		map.put("enterprise_name", evalEnterNameList);
		return map;
	}
	
	@Transactional(rollbackFor={Exception.class})
	public Map<String,Object> readExcel(Workbook workbook,String fileType) throws IOException{
		StringBuffer validInfo = null;
		// 循环工作表Sheet
		boolean validFlag = true;
		Map<String,List<Map<String,Object>>> tableMap = new HashMap<String,List<Map<String,Object>>>();
		for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
			
			List<String> evalNumList = new ArrayList<String>();
			List<String> evalNumDbList = new ArrayList<String>();
			
			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
			Sheet sheet = workbook.getSheetAt(numSheet);
			String tableName = getTableName(sheet.getSheetName());
			
			if(StringUtils.equals(tableName, "t_eval_result_query_master")) {
				Map<String,List<String>> map = getQueryMaster();
				evalNumDbList = map.get("evalNum");
			}
			
			if (sheet == null || sheet.getRow(1) == null) {
				continue;
			}
			// 循环行Row,第一行为数据中字段，第二行为字段头，第三行开始为数据
			// 获取字段名
			Row rowColumn = sheet.getRow(1);// 获取数据库中字段
			Row rowColumnName = sheet.getRow(2);//字段描述
			int columns = rowColumnName.getPhysicalNumberOfCells();
			
			for (int rowNum = 3; rowNum <= sheet.getLastRowNum(); rowNum++) {//数据第四行开始
				try {
					validInfo = new StringBuffer();
					Row row = sheet.getRow(rowNum);
					if (row == null) {
						continue;
					}
					// 循环列Cell 如： 0 企业名称， 1表单编号 ...
					Map<String, Object> param = new HashMap<String, Object>();
					for (int i = 0; i < columns; i++) {
						String name = getValue(rowColumn.getCell(i));//字段名称
						if (StringUtils.isBlank(name)) {
							//提示用户EXECL中字段有为空的
							validInfo.append(name+"不为空");
						}
						Cell cell = row.getCell(i);
						String columnName = getValue(rowColumnName.getCell(i));
						if(cell == null) {
							if(StringUtils.equals(name, "evalNum")) {
								System.err.println("第"+(rowNum+1)+"行，"+name+"值为空！");
								validInfo.append(columnName+"不为空,");
							}
							param.put(name,"");
							continue;
						}
						//防止数字变为变成科学计数法，默认都设置为string，若有小数在特殊处理
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						String value = StringUtils.trim(getValue(cell));
						if(StringUtils.isBlank(value)) {
							//提示用户EXECL中字段有为空的
							if(StringUtils.equals(name, "evalNum")) {
								validInfo.append(columnName+"不为空,");
								System.err.println("第"+(rowNum+1)+"行，"+name+"值为空！");
							}
						}else {
							if(StringUtils.equals(tableName, "t_eval_result_query_master")) {
								if(StringUtils.equals(name, "evalNum")) {
									if(evalNumList.contains(value)) {
										validInfo.append(columnName+"excel中有重复,");
									}else {
										evalNumList.add(value);
									}
								}
								if(evalNumDbList.contains(value)) {
									validInfo.append(columnName+"数据库中已经存在,");
								}
							}
						}
						param.put(name, value);
					}
					list.add(param);
					
					boolean flag  = valid(validInfo,workbook,columns,row,fileType);
					if(!flag) {
						validFlag = false;
					}
				}catch(Exception e) {
					if(log.isErrorEnabled()) {
						log.error(e.getMessage(), e);
					}
				}
			}
			
			//校验
			if(validFlag) {
				tableMap.put(tableName, list);
			}
		}
		Map<String, Object> retMap = new HashMap<String, Object>();
		
		if (!validFlag) {
			// 新建一输出文件流
			String path = CommonUtils.getDataTempPath();
			File tempFile = new File(path);
			if (!tempFile.exists()) {
				tempFile.mkdirs();
			}
			String fileName = mathRandom(10) + fileType;
			path = path + fileName;
			File fullPath = new File(path);
			if (fullPath.exists()) {
				fullPath.delete();
			}
			FileOutputStream fOut = new FileOutputStream(fullPath);
			// 把相应的Excel 工作簿存盘
			workbook.write(fOut);
			fOut.flush();
			// 操作结束，关闭文件
			fOut.close();
			workbook.close();
			
			// 返回用户导入的文件名给客户端下载使用
			retMap.put("flag",false);
			retMap.put("filePath", path);
			retMap.put("fileName", fileName);
		}else {
			for(Map.Entry<String,List<Map<String,Object>>> entry : tableMap.entrySet()) {
				String table = entry.getKey();
				List<Map<String,Object>> datas = entry.getValue();
				if(datas != null && datas.size() > 0) {
					Map<String,Object> map = datas.get(0);
					Set<String> keys = map.keySet();
					StringBuffer keyBuffer = new StringBuffer();
					StringBuffer valuesBuffer = new StringBuffer();
					for(String key : keys) {
						keyBuffer.append(key).append(",");
						valuesBuffer.append("?").append(",");
					}
					String column = keyBuffer.toString().substring(0,keyBuffer.length()-1);
					String values = valuesBuffer.toString().substring(0,valuesBuffer.length()-1);
					String sql = "insert into "+table+"("+column+") values("+values+")";
					
					commonDao.insertBatchTableData(datas, sql, column);
				}
			}
			retMap.put("flag",true);
		}
		return retMap;
	}

	public static String getTableName(String key) {
		Map<String,String> map = new HashMap<String,String>();
		
		map.put("sp主体","t_eval_result_query_master");
		map.put("机房","t_eval_result_query_house");
		map.put("链路","t_eval_result_query_link");
		map.put("承建单位","t_eval_result_query_contractor");
		
		return map.get(key);
	}
	
	public boolean valid(StringBuffer validInfo, Workbook workbook,int columns, Row row, String fileType){
		boolean validFlag = true;
		Cell addCell = null;
		// 如果存在要校验失败信息，则将校验结果追加到源文件中
		if (validInfo.length() > 0) {
			validFlag = false;
			// 生成红色字体样式
			CellStyle style1 = workbook.createCellStyle();
			Font font = workbook.createFont();
			font.setColor(HSSFColor.RED.index);
			font.setFontHeightInPoints((short) 11);
			style1.setFont(font);

			// 在当前行的colNum列上创建单元格
			addCell = row.createCell(columns);
			// 添加内容至单元格
			addCell.setCellValue(validInfo.toString());
			// 设置红色字体
			addCell.setCellStyle(style1);
		}
		return validFlag;
	}
	
	/**
	 * 得到Excel表中的值
	 * @param cell Excel中的每列
	 * @return Excel中每列的值
	 */
	@SuppressWarnings("static-access")
	public String getValue(Cell cell) {
		String value = "";
		if (cell != null) {
			if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
				value = String.valueOf(cell.getBooleanCellValue());// 返回布尔类型的值
			} else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
				value = String.valueOf(cell.getNumericCellValue());// 返回数值类型的值
			} else {
				value = String.valueOf(cell.getStringCellValue());// 返回字符串类型的值
			}
		}
		return value;
	}
	
	public static final String mathRandom(final long length) {
		double len = Math.pow(10D, length);
		long result = (long) (len * Math.random());

		// 补齐随机数长度
		while (len / result > 10) {
			result *= 10;
		}
		return Long.toString(result);
	}
}
